﻿from mydb.odbc import atable as odbctable
import xlsxwriter, xlwt


class goods:
	#This is a manipultor for export object
	#beginning of class suite
	def __init__(self, dsn='1sbdb05'):
		self.atable=odbctable()
		self.atable.table.base.dsn=dsn
		self.sql='''SELECT a.descr as "name", b.descr as "ei", a.sp137 as "type", VAL(c.value) as "price", 'ОАО "Станкоагрегат"' as "supplier", 'Москва' as "city" FROM SC156 a, SC116 b, 1SCONST c WHERE b.id=a.sp140 AND ALLTRIM(c.objid)=ALLTRIM(a.id) AND ALLTRIM(c.id)='GG6' AND a.code IN '''
	
	def extract(self, codes):
		codes_sql="('"+"', '".join(codes)+"');"
		sql=self.sql+codes_sql
		gen=self.atable.table.base.generate(sql)
		type_dict={}
		type_dict['0']='0'
		type_dict['CU']='Продукция'
		type_dict['CX']='Продукция'
		type_dict['CW']='Продукция'
		#type_dict[]=
		#type_dict[]=
		#type_dict[]=
		#type_dict[]=
		#type_dict[]=
		#type_dict[]=
		result=[]
		for row in gen:
			temp=[]
			for x in range(len(row)):
				word=row[x]
				if type(word) == str:
					word=word.strip()
				if x==2:
					if word in type_dict:
						word=type_dict[word]
					else:
						word='Неизвестный тип: '+word
				temp+=[word]
			result+=[temp]
		return result

	def write(self, file, items):
		# Create a workbook and add a worksheet.
		heads=['Наименование', 'Ед. изм.', 'Тип', 'Цена б/НДС', 'Поставщик', 'Город']
		workbook = xlwt.Workbook()
		worksheet = workbook.add_sheet('sheet')
		for x in range(len(heads)):
			worksheet.write(0, x, heads[x])
		for y in range(len(items)):
			for z in range(len(items[y])):
				worksheet.write(1+y, z, items[y][z])
		workbook.save(file)
		#workbook.save('E:\\Py\\MyPy\\progs\\nsi\\goods.xls')

class agents:
	#This is a manipultor for export object
	#beginning of class suite
	def __init__(self, dsn='1sbdb05'):
		self.atable=odbctable()
		self.atable.table.base.dsn=dsn
		self.sql='''SELECT 

PADR(SUBSTR((a.block + NVL(b.block,"") + NVL(c.block,"")),10,VAL("0x"+(ALLTRIM(SUBSTR(a.block,1,9))))),230) as "name", 
d.sp126 as "innkpp",
d.sp123 as "address",
d.sp25320 as "prim"

FROM SC133.DBF d, 

1SBLOB a 
LEFT JOIN 1SBLOB.DBF b ON a.objid=b.objid and a.fieldid=b.fieldid and b.blockno=1 
LEFT JOIN 1SBLOB.DBF c ON a.objid=c.objid and a.fieldid=c.fieldid and c.blockno=2 

WHERE alltrim(a.fieldid)="3E" and a.blockno=0 and d.id=a.objid and ALLTRIM(d.code) IN '''

	def extract(self, codes):
		codes_sql="('"+"', '".join(codes)+"');"
		sql=self.sql+codes_sql
		gen=self.atable.table.base.generate(sql)
		result=[]
		for row in gen:
			temp=[]
			for x in range(len(row)):
				word=row[x].strip()
				if x==1:
					if '/' in word:
						islash=word.index('/')
						temp+=[word[0:islash]]
						temp+=[word[islash+1:]]
					else: 
						temp+=[word]
						temp+=['']
				else:
					temp+=[word]
			result+=[temp]
		return result
	
	def write(self, file, items):
		# Create a workbook and add a worksheet.
		heads=['Наименование', 'ИНН', 'КПП', 'Адрес', 'Примечание']
		workbook = xlsxwriter.Workbook(file)
		worksheet = workbook.add_worksheet()
		for x in range(len(heads)):
			worksheet.write(0, x, heads[x])
		for y in range(len(items)):
			for z in range(len(items[y])):
				worksheet.write(1+y, z, items[y][z])
		workbook.close()
		

class nomens:
	#This is a manipultor for export object
	#beginning of class suite
	def __init__(self, dsn='alldata'):
		self.atable=odbctable()
		self.atable.table.base.dsn=dsn
		self.sql='''SELECT a.nom_naml, b.ei_name, c.name, STR(a.Cena) FROM cen_pc a, ei b, classes c WHERE a.Ei_kod1=b.ei_kod AND ALLTRIM(c.Code)=SUBSTR(a.nom_nom,1,2) AND ALLTRIM(a.nom_nom) IN '''
	
	def extract(self, codes):
		codes_sql="('"+"', '".join(codes)+"');"
		sql=self.sql+codes_sql
		gen=self.atable.table.base.generate(sql)
		result=[]
		for row in gen:
			temp=[]
			for x in range(len(row)):
				temp+=[row[x].strip().strip('_')[0]+row[x].strip().strip('_')[1:].lower()]
			result+=[temp]
		return result
	
	def write(self, file, items, agent):
		# Create a workbook and add a worksheet.
		heads=['Наименование', 'Ед. изм.', 'Группа', 'Цена б/НДС', 'Поставщик', 'Город']
		workbook = xlwt.Workbook()
		worksheet = workbook.add_sheet('sheet')
		for x in range(len(heads)):
			worksheet.write(0, x, heads[x])
		for y in range(len(items)):
			for z in range(len(items[y])):
				worksheet.write(1+y, z, items[y][z])
			worksheet.write(1+y, z+1, agent[0])
			worksheet.write(1+y, z+2, agent[1])
		workbook.save(file)